package com.whu.exp1jsp.dao;

import com.whu.exp1jsp.pojo.ProductSt;
import com.whu.exp1jsp.util.DBUtil;

import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {

    // 发布商品
    public int insertProduct(ProductSt product) throws SQLException {
        String sql = "INSERT INTO product_st (product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, product.getProductTitle());
            ps.setString(2, product.getProductIntro());
            ps.setDouble(3, product.getProductPrice());
            ps.setString(4, product.getProductImgs());
            ps.setTimestamp(5, Timestamp.from(Instant.now()));
            ps.setLong(6, product.getOwnerId());
            ps.setString(7, product.getLocation());
            ps.setInt(8, 1);
            return ps.executeUpdate();
        }
    }

    // 删除商品
    public int deleteProduct(long productId) throws SQLException {
        String sql = "DELETE FROM product_st WHERE product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, productId);
            return ps.executeUpdate();
        }
    }

    // 修改商品
    public int updateProduct(ProductSt product) throws SQLException {
        String sql = "UPDATE product_st SET product_title = ?, product_intro = ?, product_price = ?, product_imgs = ?, publish_time = ?, location = ?, status = ? " +
                "WHERE product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, product.getProductTitle());
            ps.setString(2, product.getProductIntro());
            ps.setDouble(3, product.getProductPrice());
            ps.setString(4, product.getProductImgs());
            ps.setTimestamp(5, Timestamp.valueOf(product.getPublishTime()));
            ps.setString(6, product.getLocation());
            ps.setInt(7, product.getStatus());
            ps.setLong(8, product.getProductId());
            return ps.executeUpdate();
        }
    }


    // 查询商品
    public ProductSt selectProductById(long productId) throws SQLException {
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st WHERE product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, productId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    );
                } else {
                    return null;
                }
            }
        }
    }


    public List<ProductSt> selectAllAvailableProducts() throws SQLException{
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st WHERE status = 1 " +
                "ORDER BY publish_time DESC ";
        List<ProductSt> productList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    productList.add(new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    ));
                }
            }
        }
        return productList;
    }

    public List<ProductSt> selectAllProducts() throws SQLException{
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st ORDER BY publish_time DESC";
        List<ProductSt> productList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    productList.add(new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    ));
                }
            }
        }
        return productList;
    }

    // 查询所有商品，按最新到最老排序
    public List<ProductSt> selectAllProducts(int startIndex, int length) throws SQLException {
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st ORDER BY publish_time DESC LIMIT ? OFFSET ?";
        List<ProductSt> productList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, length);
            ps.setInt(2, startIndex);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    productList.add(new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    ));
                }
            }
        }
        return productList;
    }

    // 修改商品状态
    public int updateProductStatus(long productId, int status) throws SQLException {
        String sql = "UPDATE product_st SET status = ? WHERE product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, status);
            ps.setLong(2, productId);
            return ps.executeUpdate();
        }
    }

    // 查询某一用户发布的所有商品
    public List<ProductSt> selectProductsByOwnerId(long ownerId) throws SQLException {
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st WHERE owner_id = ? ORDER BY publish_time DESC";
        List<ProductSt> productList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, ownerId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    productList.add(new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    ));
                }
            }
        }
        return productList;
    }

    // 查询某一时间段内发布的所有商品
    public List<ProductSt> selectProductsByTime(String startTime, String endTime) throws SQLException {
        String sql = "SELECT product_id, product_title, product_intro, product_price, product_imgs, publish_time, owner_id, location, status " +
                "FROM product_st WHERE publish_time BETWEEN ? AND ? ORDER BY publish_time DESC";
        List<ProductSt> productList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setTimestamp(1, Timestamp.valueOf(startTime));
            ps.setTimestamp(2, Timestamp.valueOf(endTime));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    productList.add(new ProductSt(
                            rs.getLong("product_id"),
                            rs.getString("product_title"),
                            rs.getString("product_intro"),
                            rs.getDouble("product_price"),
                            rs.getString("product_imgs"),
                            rs.getTimestamp("publish_time").toLocalDateTime(),
                            rs.getLong("owner_id"),
                            rs.getString("location"),
                            rs.getInt("status")
                    ));
                }
            }
        }
        return productList;
    }
}
